import pg
import document

class database:

	def __init__(self):

		self.connex = pg.connect()
		self.sql = ""

        
	def insert_doc(self, file_name):

		self.sql = "INSERT INTO document (doc_name) VALUES ('" + file_name + "') RETURNING doc_id;"
		teste = self.connex.query(self.sql)
		if teste == None:
			return None
		doc_id = teste.dictresult()[0]['doc_id']
		return document.document(doc_id)


	def insert_ancestor(self, document, element):

		doc_id = str(document.get_document_id())
		node_name = element.get_name()
		level = str(element.get_level())
		minsib = str(element.get_min_sib_order())
		maxsib = str(element.get_max_sib_order())
		local_order = str(element.get_local_order())

		self.sql = "INSERT INTO AncestorInfo (Doc_ID, NodeName, NodeLevel, MinSibOrder, MaxSibOrder, LocalOrder) VALUES (" + doc_id + ", '" + node_name + "', " + level + ", " + minsib + ", " + maxsib + ", " + local_order + ");"
		self.connex.query(self.sql)


	def insert_leaf(self, document, element, text):

		path_id = str(self.insert_path(element.get_path()))
		doc_id = str(document.get_document_id())
		leaf_order = str(document.add_leaf())
		leaf_value = text;
		local_order = str(element.get_local_order());
		level = str(element.get_level())
		sib_order = str(element.get_sib_order())
		
		self.sql = """INSERT INTO LeafValue (Doc_ID, LeafOrder, Path_ID, SiblingOrder, 
                              LeafValue, LocalOrder, Level) VALUES ("""
		self.sql = self.sql + doc_id + ", " + leaf_order + ", " + path_id + ", " + sib_order
		self.sql = self.sql + ", '" + leaf_value + "', " + local_order + ", " + level + ");"
		self.connex.query(self.sql)	


	def insert_path(self, path):

		self.sql = "SELECT Path_ID FROM Path WHERE Path_Exp = '" + path + "';"
		teste = self.connex.query(self.sql)
		if teste.dictresult() == []:
			self.sql = "INSERT INTO Path (Path_Exp) VALUES ('" + path + "') RETURNING Path_ID;"
			teste = self.connex.query(self.sql)
			if teste == None:
				return None
		path_id = teste.dictresult()[0]['path_id']
		return path_id

	
	def query_base(self, query):

		result = self.connex.query(query)
		return result

	def is_root_node_matched(self,doc_id_1,doc_id_2):

		self.sql = """
		  SELECT 
		    A1.nodename
	          FROM 
		    ancestorinfo as A1, 
		    ancestorinfo as A2 
	          WHERE 
		    A1.nodelevel = 1 AND 
		    A2.nodelevel = 1 AND
		    A1.doc_id = """ + str(doc_id_1) + """ AND
		    A2.doc_id = """ + str(doc_id_2) + """ AND
		    A1.nodename = A2.nodename
                """

	        result = self.connex.query(self.sql)

	       	return result.ntuples()


	def define_matching_leaf_nodes(self,doc_id_1,doc_id_2):

		fixed_leaf_node = """
		  SELECT
		    l1.level, l1.siblingorder AS so1,
		    l2.siblingorder AS so2,
		    COUNT(l1.siblingorder)*2 AS counter
	          FROM leafvalue AS l1, leafvalue AS l2
		  WHERE
		    l1.doc_id = """ + str(doc_id_1) + """ AND
		    l2.doc_id = """ + str(doc_id_2) + """ AND
		    l1.path_id = l2.path_id AND
		    l1.leafvalue = l2.leafvalue AND
		    l1.localorder = l2.localorder
	          GROUP BY 
                    l1.level, l1.siblingorder,
		    l2.siblingorder
		"""

	        shifted_leaf_node = """
		  SELECT
		    l1.level, l1.siblingorder AS so1,
		    l2.siblingorder AS so2,
		    COUNT(l1.siblingorder) AS counter
	          FROM leafvalue AS L1, leafvalue AS l2
		  WHERE
		    l1.doc_id = """ + str(doc_id_1) + """ AND
		    l2.doc_id = """ + str(doc_id_2) + """ AND
		    l1.path_id = l2.path_id AND
		    l1.leafvalue = l2.leafvalue AND
		    (l1.level, l1.siblingorder) NOT IN
		      (SELECT level, so1 FROM (""" + fixed_leaf_node + """) AS fixedlv ) AND
		    (l2.level, l2.siblingorder) NOT IN
		      (SELECT level, so2 FROM (""" + fixed_leaf_node + """) AS fixedlv )
	          GROUP BY l1.level, l1.siblingorder, l2.siblingorder
                """

	        self.mln = "((" + shifted_leaf_node + ") UNION (" + fixed_leaf_node + "))"


	def find_matchig_sibling_order(self,doc_id_1,doc_id_2):

		fmso = """(
		  SELECT
		    d.level, d.so1, d.so2,
		    d.counter AS counter,
		    v1.total+v2.total AS total
	          FROM
		    (SELECT
		       f.level, f.so1, f.so2,
		       f.counter
		     FROM 
		       """ + self.mln + """ AS f
		     ) AS d,
		       (SELECT l1.level, l1.siblingorder,
			  COUNT(l1.leaforder) AS total
			FROM leafvalue AS l1
			WHERE l1.doc_id = """ + str(doc_id_1) + """
			GROUP BY l1.level,
			  l1.siblingorder) AS v1,
		       (SELECT l1.level, l1.siblingorder,
			  COUNT(l1.leafvalue) AS total
			FROM leafvalue AS l1
			WHERE l1.doc_id = """ + str(doc_id_2) + """
			GROUP BY l1.level,
			  l1.siblingorder) AS v2
	          WHERE
		    v1.level = d.level AND
		    v2.level = d.level AND
		    v1.siblingorder = d.so1 AND
		    v2.siblingorder = d.so2)
	        """

	        self.sql = "INSERT INTO tempso (level,so1,so2,counter,total) (" + fmso + ")"

	        result = self.connex.query(self.sql)
		
	
	def get_internal_node_max_level(self,doc_id_1,doc_id_2):

		self.sql = """
                  SELECT MAX(nodelevel) AS max_level
                  FROM ancestorinfo
                  WHERE 
                    doc_id = """ + str(doc_id_1) + """ OR
                    doc_id = """ + str(doc_id_2)

		result = self.connex.query(self.sql)
		return result.dictresult()[0]['max_level']


	def find_p_matching_int_nodes(self,doc_id_1,doc_id_2,level):

		fpmin = """
		  SELECT
		    a1.minsiborder AS minso1,
		    a1.maxsiborder AS maxso1,
		    a2.minsiborder AS minso2,
		    a2.maxsiborder AS maxso2,
                    a1.nodelevel AS level,
                    a1.localorder AS lo1,
                    a2.localorder AS lo2,
                    0 AS flag,
		    SUM(t.counter) AS counter,
		    SUM(t.total) AS total,
                    SUM(t.counter)/SUM(t.total) AS score
	          FROM
		    ancestorinfo AS a1, ancestorinfo AS a2,
		    tempso AS t
	          WHERE
		    a1.doc_id = """ + str(doc_id_1) + """ AND
		    a2.doc_id = """ + str(doc_id_2) + """ AND
		    t.so1 BETWEEN a1.minsiborder AND
		    a1.maxsiborder AND
		    t.so2 BETWEEN a2.minsiborder AND
		    a2.maxsiborder AND
		    a1.nodelevel = a2.nodelevel AND
		    a1.nodename = a2.nodename AND
		    a1.nodelevel = """ + str(level) + """
	          GROUP BY 
                    a1.nodelevel,
		    a1.minsiborder, 
                    a1.maxsiborder,
		    a2.minsiborder, 
                    a2.maxsiborder,
                    a1.localorder,
                    a2.localorder
	        """

	        self.sql = "INSERT INTO tempmatching (" + fpmin + ")"

	        result = self.connex.query(self.sql)


	def get_non_121_matching(self,doc_id_1,doc_id_2,level):

		self.sql = "SELECT * FROM tempmatching WHERE level = " + str(level) + " AND counter < total"

		result = self.connex.query(self.sql)
		
		return result


	def generate_score_matrix(self,data):

	        self.sql = """SELECT minso1,minso2,score FROM tempmatching
                            WHERE minso1 BETWEEN """ + str(data["minso1"]) + " AND " + str(data["maxso1"]) + """ AND
                                  minso2 BETWEEN """ + str(data["minso2"]) + " AND " + str(data["maxso2"]) + """ AND
                                  level  = """ + str(data["level"] + 1) + " AND counter < total"

	        result = self.connex.query(self.sql).dictresult()
				  
	        ret = dict()

		test1 = dict()
		test2 = dict()
		cont1 = 0
		cont2 = 0

		for r in result:

			val1 = r["minso1"]
			val2 = r["minso2"]
			score = r["score"]
			
			if val1 not in test1:
				

#				print "XML 1"
#				print val1,cont1

				test1[val1] = cont1
				cont1 = cont1 + 1

			if val2 not in test2:

#				print "XML 2"
#				print val2,cont2

				test2[val2] = cont2
				cont2 = cont2 + 1
				
			val1 = test1[val1]
			val2 = test2[val2]
			
			if val1 in ret:		
					
				ret[val1][val2] = [score,0.0,[],[]]

			else:
				
				ret[val1] = {val2:[score,0.0,[],[]]}

		for i in ret:

			j = 0

			while j < cont2:
				
				if j not in ret[i]:
					
					ret[i][j] = [0.0,0.0,[],[]]

				j = j + 1

		return (ret,result)


	def best_combination_finder(self,score_matrix):

		max_score = 0
		best_combination = []
		best_other_combination = []

		if score_matrix == {}:
			return (max_score,best_combination,best_other_combination)
		
		for i in score_matrix:

			for j in score_matrix[i]:

				if score_matrix[i][j][0] > 0.0:

					if i == 0:
						
						score_matrix[i][j][2].append(j)
						score_matrix[i][j][2].append(i)
						score_matrix[i][j][1] = score_matrix[i][j][0]
						if score_matrix[i][j][1] > max_score:
							max_score = score_matrix[i][j][1]
							best_combination = score_matrix[i][j][2]
							best_other_combination = score_matrix[i][j][3]

					else:

						for k in score_matrix[i]:

							if ((j not in score_matrix[i-1][k][2]) and (k not in score_matrix[i-1][k][3]) and
							    (score_matrix[i][j][1] < (score_matrix[i][j][0] + score_matrix[i-1][k][1]))):
								
								score_matrix[i][j][1] = score_matrix[i][j][0] + score_matrix[i-1][k][1]
								
								score_matrix[i][j][2] = score_matrix[i-1][k][2]
								score_matrix[i][j][3] = score_matrix[i-1][k][3]
								
								score_matrix[i][j][2].append(j)
								score_matrix[i][j][3].append(i)
								
							if ((j <> k) and (score_matrix[i][j][1] < 
									  (score_matrix[i][j][0] + score_matrix[i-1][k][0]))):

								score_matrix[i][j][1] = score_matrix[i][j][0] + score_matrix[i-1][k][0]
								
								score_matrix[i][j][2] = [j]
								score_matrix[i][j][3] = [i]

						if score_matrix[i][j][1] > max_score:

							max_score = score_matrix[i][j][1]

							best_combination = score_matrix[i][j][2]
							best_other_combination = score_matrix[i][j][3]

#							print max_score,score_matrix[i][j][2],best_other_combination

		return (max_score,best_combination,best_other_combination)


	def maximize_similarity_score(self,doc_id_1,doc_id_2,level):

		data = self.get_non_121_matching(doc_id_1,doc_id_2,level).dictresult()

		for d in data:

			(score_matrix,control_matrix) = self.generate_score_matrix(d)

			(max_score,max_score_combination,max_score_other_combination) = self.best_combination_finder(score_matrix)

			print max_score,max_score_combination,max_score_other_combination

				
	def find_best_matching_subtrees(self,doc_id_1,doc_id_2,theta):

		if not self.is_root_node_matched(doc_id_1,doc_id_2):
			return

		self.define_matching_leaf_nodes(doc_id_1,doc_id_2)

		self.find_matchig_sibling_order(doc_id_1,doc_id_2)

		max_level = self.get_internal_node_max_level(doc_id_1,doc_id_2)

		i = max_level		
		while (i > 0):

			self.find_p_matching_int_nodes(doc_id_1,doc_id_2,i)

			self.maximize_similarity_score(doc_id_1,doc_id_2,i)
			
			i = i - 1
		
		
